package com.sundaying.gooway.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sundaying.gooway.model.UrlItem;
import com.sundaying.gooway.util.DbTool;

/**
 * <p>
 * 网址相关业务操作
 * </p>
 * 
 * @author liuhj
 * @version 1.0.0.1
 */
public class UrlItemService {
	/**
	 * 添加网址
	 * 
	 * @param urlItem
	 *            网址对象
	 * @return boolean 是否添加成功
	 */
	public boolean add(UrlItem urlItem) {
		try {
			Connection conn = DbTool.createConn();
			String sql = "insert into GOW_URLITEM(URL, TITLE, CLASSID) values(?, ?, ?)";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setString(1, urlItem.getUrl());
				ps.setString(2, urlItem.getTitle());
				ps.setInt(3, urlItem.getClassID());
				ps.executeUpdate();
				System.out.println("添加网址成功：" + urlItem.getUrl());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);

			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return false;
	}

	/**
	 * 获取所有网址集合对象
	 * 
	 * @return List 网址集合对象
	 */
	public List<UrlItem> list() {
		List<UrlItem> urlItems = new ArrayList();

		Connection conn = DbTool.createConn();
		// 按类别、序号顺序显示
		String sql = "select A.*, B.CLASSNAME from GOW_URLITEM A LEFT JOIN GOW_URLCLASS B ON A.CLASSID=B.CLASSID order by classid, ordernum";
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ResultSet rs = ps.executeQuery();
			UrlItem urlItem = null;
			while (rs.next()) {
				urlItem = new UrlItem();
				urlItem.setItemID(rs.getInt("ITEMID"));
				urlItem.setUrl(rs.getString("URL"));
				urlItem.setTitle(rs.getString("TITLE"));
				urlItem.setClassID(rs.getInt("CLASSID"));
				urlItem.setClassName(rs.getString("CLASSNAME"));
				urlItems.add(urlItem);
			}
			System.out.println("查询返回记录数：" + urlItems.size());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return urlItems;
	}

	/**
	 * 删除某个网址对象
	 * 
	 * @param UrlItem
	 *            网址对象
	 * @return boolean 是否删除成功
	 */
	public boolean delete(UrlItem urlItem) {
		return delete(urlItem.getItemID());
	}

	/**
	 * 删除某个网址对象
	 * 
	 * @param String
	 *            网址对象的ID
	 * @return boolean 是否删除成功
	 */
	public boolean delete(int itemID) {
		try {
			Connection conn = DbTool.createConn();
			String sql = "delete from GOW_URLITEM where ITEMID = ?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setInt(1, itemID);
				ps.executeUpdate();
				System.out.println("已删除网址ID：" + itemID);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	/**
	 * 获取某个网址对象
	 * 
	 * @param String
	 *            网址的ID
	 * @return UrlItem 网址对象
	 */
	public UrlItem loadByID(int itemID) {
		UrlItem urlItem = null;
		try {
			Connection conn = DbTool.createConn();
			String sql = "select * from GOW_URLITEM where ITEMID = ?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setInt(1, itemID);
				ResultSet rs = ps.executeQuery();

				if (rs.next()) {
					urlItem = new UrlItem();
					urlItem.setItemID(rs.getInt("ITEMID"));
					urlItem.setUrl(rs.getString("URL"));
					urlItem.setTitle(rs.getString("TITLE"));
					urlItem.setClassID(rs.getInt("CLASSID"));
				}
				System.out.println("查询返回网址：" + urlItem.getUrl());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return urlItem;
	}

	/**
	 * 更新某个网址的属性
	 * 
	 * @param UrlItem
	 *            网址对象
	 * @return boolean 是否更新成功
	 */
	public boolean update(UrlItem urlItem) {
		try {
			Connection conn = DbTool.createConn();
			String sql = "update GOW_URLITEM set URL=?, TITLE=?, CLASSID=? where ITEMID=?";
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ps.setString(1, urlItem.getUrl());
				ps.setString(2, urlItem.getTitle());
				ps.setInt(3, urlItem.getClassID());
				ps.setInt(4, urlItem.getItemID());
				ps.executeUpdate();
				System.out.println("已更新网址ID：" + urlItem.getItemID());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<UrlItem> list(int page, int rows, String urlClassID) {
		List<UrlItem> urlItems = new ArrayList();
		if (urlClassID.trim() == "") {
			urlItems = this.list(page, rows);
		} else {
			Connection conn = DbTool.createConn();
			String sql = "select A.*, B.CLASSNAME from GOW_URLITEM A LEFT JOIN GOW_URLCLASS B ON A.CLASSID=B.CLASSID where a.classid in (" + urlClassID + ") order by A.CLASSID LIMIT "
					+ ((page - 1) * rows + 1) + "," + rows;
			PreparedStatement ps = DbTool.prepare(conn, sql);
			try {
				ResultSet rs = ps.executeQuery();
				UrlItem urlItem = null;
				while (rs.next()) {
					urlItem = new UrlItem();
					urlItem.setItemID(rs.getInt("ITEMID"));
					urlItem.setUrl(rs.getString("URL"));
					urlItem.setTitle(rs.getString("TITLE"));
					urlItem.setClassID(rs.getInt("CLASSID"));
					urlItem.setClassName(rs.getString("CLASSNAME"));
					urlItems.add(urlItem);
				}
				System.out.println("查询返回记录数3：" + urlItems.size());
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DbTool.close(ps);
			DbTool.close(conn);
		}
		return urlItems;
	}

	public List<UrlItem> list(int page, int rows) {
		List<UrlItem> urlItems = new ArrayList();

		Connection conn = DbTool.createConn();
		String sql = "select A.*, B.CLASSNAME from GOW_URLITEM A LEFT JOIN GOW_URLCLASS B ON A.CLASSID=B.CLASSID order by A.CLASSID LIMIT "
				+ ((page - 1) * rows + 1) + "," + rows;
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ResultSet rs = ps.executeQuery();
			UrlItem urlItem = null;
			while (rs.next()) {
				urlItem = new UrlItem();
				urlItem.setItemID(rs.getInt("ITEMID"));
				urlItem.setUrl(rs.getString("URL"));
				urlItem.setTitle(rs.getString("TITLE"));
				urlItem.setClassID(rs.getInt("CLASSID"));
				urlItem.setClassName(rs.getString("CLASSNAME"));
				urlItems.add(urlItem);
			}
			System.out.println("查询返回记录数3：" + urlItems.size());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return urlItems;
	}

	public int getCount() {
		int iCount = 0;
		Connection conn = DbTool.createConn();
		String sql = "select count(*) count from GOW_URLITEM A";
		PreparedStatement ps = DbTool.prepare(conn, sql);
		try {
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				iCount = rs.getInt("count");
			}
			System.out.println("总行数：" + iCount);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbTool.close(ps);
		DbTool.close(conn);

		return iCount;
	}
}
